package com.gack.business.dao;

import java.util.List;

import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.Id;
import javax.persistence.Query;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

/**
 * 
 * @author ws
 * 2018-8-20
 */
@Repository
public class InviteToEnterpriseDao {
	
	@Autowired
	private EntityManager em;
	
	public List<InviteToEnterpriseDao.Friend> friendList(String inviterId, String enterpriseId){
		String jpql = "select u.id,"
				+ " (select username from user where id=u.id) as username,"
				+ " case when (select friend_remarks from contacts where type=1 and user_userid=:userId and userid_friend_id=u.id) is not null"
				+ " then (select friend_remarks from contacts where type=1 and user_userid=:userId and userid_friend_id=u.id)"
				+ " when (select enterprise_remarks from user_enterprise_department_position where enterprise_id=:enterpriseId and user_id=u.id) is not null"
				+ " then (select enterprise_remarks from user_enterprise_department_position where enterprise_id=:enterpriseId and user_id=u.id)"
				+ " else u.nickname"
				+ " end as name_for_show,"
				+ " u.portrait,"
				+ " case when (select count(id) from user_enterprise_department_position where enterprise_id=:enterpriseId and user_id=u.id) = 1"
				+ " then 1"
				+ " when (select create_time from invitetoenterpriserecord where enterprise_id=:enterpriseId and inviter_id=:userId and invitee_username=u.username and status=2 order by create_time desc limit 1) > (now() - interval 24 hour)"
				+ " then 2"
				+ " else 3"
				+ " end as status"
				+ " from user u"
				+ " where u.id in ("
				+ " select userid_friend_id"
				+ " from contacts"
				+ " where type=1"
				+ " and user_userid=:userId"
				+ ")";
		
		Query query = em.createNativeQuery(jpql, InviteToEnterpriseDao.Friend.class);
		query.setParameter("userId", inviterId);
		query.setParameter("enterpriseId", enterpriseId);
		
		return query.getResultList();
	}
	
	/**
	 * 已被邀请的人员手机号
	 * @param inviterId 邀请人id
	 * @param enterpriseId 公司id
	 * @return
	 */
	public List<String> usernameAboutWaitingForPass(String inviterId, String enterpriseId){
		String jpql = "select distinct invitee_username"
				+ " from invitetoenterpriserecord"
				+ " where enterprise_id=:enterpriseId"
				+ " and inviter_id=:userId"
				+ " and status=2"
				+ " and create_time > (now() - interval 24 hour)"
				+ " and invitee_username not in (select username"
											+ " from user"
											+ " where id in (select user_id"
														+ " from user_enterprise_department_position"
														+ " where enterprise_id=:enterpriseId)"
											+ ")";
		Query query = em.createNativeQuery(jpql);
		query.setParameter("userId", inviterId);
		query.setParameter("enterpriseId", enterpriseId);
		
		return query.getResultList();
	}
	
	/**
	 * 找出已加入公司的手机号
	 * @param enterpriseId 公司id
	 * @param inviteeIds 被邀请者id
	 * @return 已加入公司的手机号集合
	 */
	public List<String> hasInEnterpriseByUserIds(String enterpriseId, String inviteeIds){
		String jpql = "select username"
				+ " from User"
				+ " where id in (select userId"
							+ " from UserEnterpriseDepartmentPosition"
							+ " where enterpriseId=:enterpriseId"
							+ " and userId in ";
		
		String[] inviteeIdArray = inviteeIds.split(";");
		StringBuilder sb = new StringBuilder("(");
		for(String element : inviteeIdArray){
			sb.append("'" + element + "', ");
		}
		sb = new StringBuilder(sb.substring(0, sb.length()-", ".length()));
		sb.append("))");
		jpql += sb.toString();
		
		Query query = em.createQuery(jpql);
		query.setParameter("enterpriseId", enterpriseId);
		
		return query.getResultList();
	}
	
	/**
	 * 找出已加入公司的手机号
	 * @param enterpriseId 公司id
	 * @param inviteeUsernames 被邀请者手机号
	 * @return 已加入公司的手机号集合
	 */
	public List<String> hasInEnterpriseByUsernames(String enterpriseId, String inviteeUsernames){
		String jpql = "select username"
				+ " from User"
				+ " where id in (select userId"
							+ " from UserEnterpriseDepartmentPosition"
							+ " where enterpriseId=:enterpriseId)"
				+ " and username in ";
		
		String[] inviteeUsernameArray = inviteeUsernames.split(";");
		StringBuilder sb = new StringBuilder("(");
		for(String element : inviteeUsernameArray){
			sb.append("'" + element + "', ");
		}
		sb = new StringBuilder(sb.substring(0, sb.length()-", ".length()));
		sb.append(")");
		jpql += sb.toString();
		
		Query query = em.createQuery(jpql);
		query.setParameter("enterpriseId", enterpriseId);
		
		return query.getResultList();
	}
	
	/**
	 * 找出已被邀请过的手机号
	 * @param inviterId 邀请者id
	 * @param enterpriseId 公司id
	 * @param inviteeIds 被邀请者id(id1;id2;id3)
	 * @return
	 */
	public List<String> hasInvitedByUserIds(String inviterId, String enterpriseId, String inviteeIds){
		String jpql = "select distinct invitee_username"
				+ " from invitetoenterpriserecord"
				+ " where enterprise_id=:enterpriseId"
				+ " and inviter_id=:userId"
				+ " and status=2"
				+ " and create_time > (now() - interval 24 hour)"
				+ " and invitee_username not in (select username"
											+ " from user"
											+ " where id in (select user_id"
														+ " from user_enterprise_department_position"
														+ " where enterprise_id=:enterpriseId)"
											+ ")"
				+ " and invitee_username in (select username"
										+ " from user"
										+ " where id in ";
		
		String[] inviteeIdArray = inviteeIds.split(";");
		StringBuilder sb = new StringBuilder("(");
		for(String element : inviteeIdArray){
			sb.append("'" + element + "', ");
		}
		sb = new StringBuilder(sb.substring(0, sb.length()-", ".length()));
		sb.append("))");
		jpql += sb.toString();
		
		Query query = em.createNativeQuery(jpql);
		query.setParameter("userId", inviterId);
		query.setParameter("enterpriseId", enterpriseId);
		
		return query.getResultList();
	}
	
	/**
	 * 查找已被邀请过的手机号
	 * @param inviterId 邀请者id
	 * @param enterpriseId 公司id
	 * @param inviteeUsernames 被邀请者手机号
	 * @return
	 */
	public List<String> hasInvitedByUsernames(String inviterId, String enterpriseId, String inviteeUsernames){
		String jpql = "select distinct invitee_username"
				+ " from invitetoenterpriserecord"
				+ " where enterprise_id=:enterpriseId"
				+ " and inviter_id=:userId"
				+ " and status=2"
				+ " and create_time > (now() - interval 24 hour)"
				+ " and invitee_username not in (select username"
											+ " from user"
											+ " where id in (select user_id"
														+ " from user_enterprise_department_position"
														+ " where enterprise_id=:enterpriseId)"
											+ ")"
				+ " and invitee_username in ";
		
		String[] inviteeUsernameArray = inviteeUsernames.split(";");
		StringBuilder sb = new StringBuilder("(");
		for(String element : inviteeUsernameArray){
			sb.append("'" + element + "', ");
		}
		sb = new StringBuilder(sb.substring(0, sb.length()-", ".length()));
		sb.append(")");
		jpql += sb.toString();
		
		Query query = em.createNativeQuery(jpql);
		query.setParameter("userId", inviterId);
		query.setParameter("enterpriseId", enterpriseId);
		
		return query.getResultList();
	}
	
	@Getter
	@Setter
	@NoArgsConstructor
	@AllArgsConstructor
	@Entity
	public static class Friend{
		@Id
		private String id; //用户id
		private String name_for_show; //若有好友备注,则显示好友备注,否则显示企业备注或昵称
		private String username;//手机号
		private String portrait; //头像地址
		private Integer status; //1:已进入公司  2:已邀请，等待验证通过   3:未进入公司，并可以发送邀请
	}
	
}
